package edu.student.models;

import java.util.ArrayList;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.util.DB;

public class Subject {
	
	private int subjectId;
	private String subjectName;
	private String subjectCode;
	private int facultyID;
	private String facultyName;
	private String semesterName;
	private String isEnrolled;
	private String grade;
		
	/*code for fetching subject name for a subject id*/
	public static String getSubjectName(int subjectIdTemp) {
		//ArrayList<String> semesters = new ArrayList<String>();
		ResultSet resultSet = null;
		String subjectNameTemp="";
		String query = "select subject_name from subject where subject_id=" + "'"+subjectIdTemp+"'" ;
		Connection connection = DB.getConnection();
		resultSet = DB.readFromDB(query, connection);
		try {
			if (resultSet.next()) {
				subjectNameTemp =resultSet.getString("subject_name");
			}
		} catch (SQLException e) {
	       System.out.println("Exception while reading from db"+ e);
		}
		DB.close(connection);
		return subjectNameTemp;
	}
	/*code for fetching subject name for a subject id*/
	public static int getSubjectId(String subjectNameTemp) {
		//ArrayList<String> semesters = new ArrayList<String>();
		ResultSet resultSet = null;
		int subjectIdTemp=0;
		String query = "select subject_id from subject where subject_name=" + "'"+subjectNameTemp+"'" ;
		Connection connection = DB.getConnection();
		resultSet = DB.readFromDB(query, connection);
		try {
			if (resultSet.next()) {
				subjectIdTemp =resultSet.getInt("subject_id");
			}
		} catch (SQLException e) {
	       System.out.println("Exception while reading from db"+ e);
		}
		DB.close(connection);
		return subjectIdTemp;
	}
	
	//Returns all subjects in the database. 
	public static ArrayList<Subject> findAllSubjectsInDatabase(String rollNum){
	
		ArrayList<Subject> selection = new ArrayList<Subject>();
		ResultSet result = null;
		Connection con = DB.getConnection();
		String query = "SELECT SUBJECT.SUBJECT_CODE, SUBJECT.SUBJECT_NAME, FACULTY.FACULTY_NAME, SEMESTER.SEMESTER_NAME,"
				+ "ENROLLMENT.IS_ENROLLED, ENROLLMENT.GRADE "
				+ "FROM SUBJECT, FACULTY, SEMESTER, ENROLLMENT " 
				+ "WHERE ENROLLMENT.SUBJECT_ID = SUBJECT.SUBJECT_ID AND SUBJECT.FACULTY_ID = FACULTY.FACULTY_ID "
				+ "AND ENROLLMENT.SEMESTER_ID=SEMESTER.SEMESTER_ID "
				+ "AND ENROLLMENT.ROLL_NO = '" + rollNum + "' ";
		result = DB.readFromDB(query, con);
		try {
			while (result.next()) {
				Subject sub = new Subject();
				sub.subjectCode = result.getString("SUBJECT.SUBJECT_CODE");
				sub.subjectName = result.getString("SUBJECT.SUBJECT_NAME");
				sub.facultyName = result.getString("FACULTY.FACULTY_NAME");
				sub.semesterName = result.getString("SEMESTER.SEMESTER_NAME");
				sub.isEnrolled = result.getString("ENROLLMENT.IS_ENROLLED");
				sub.grade = result.getString("ENROLLMENT.GRADE");
				selection.add(sub);		
			}
		} catch (SQLException e) {
	       System.out.println("Exception while reading from db"+ e);
		}
		DB.close(con);
	
		return selection;
	}
		public static ArrayList<Subject> findAllSubjectsInDatabase(){
		
		ArrayList<Subject> selection = new ArrayList<Subject>();
		ResultSet result = null;
		Connection con = DB.getConnection();
		String query = "SELECT SUBJECT_CODE , SUBJECT_NAME FROM SUBJECT ";
				
		result = DB.readFromDB(query, con);
		try {
			while (result.next()) {
				Subject sub = new Subject();
				sub.subjectCode = result.getString("SUBJECT.SUBJECT_CODE");
				sub.subjectName = result.getString("SUBJECT.SUBJECT_NAME");
				
				selection.add(sub);		
			}
		} catch (SQLException e) {
	       System.out.println("Exception while reading from db"+ e);
		}
		DB.close(con);
	
		return selection;
	}
	
	public int getSubjectId() {
		return subjectId;
	}
	public void setSubjectId(int subjectId) {
		this.subjectId = subjectId;
	}
	public String getSubjectName() {
		return subjectName;
	}
	public void setSubjectName(String subjectName) {
		this.subjectName = subjectName;
	}
	public String getSubjectCode() {
		return subjectCode;
	}
	public void setSubjectCode(String subjectCode) {
		this.subjectCode = subjectCode;
	}
	public int getFacultyID() {
		return facultyID;
	}
	public void setFacultyID(int facultyID) {
		this.facultyID = facultyID;
	}
	public String getFacultyName() {
		return facultyName;
	}
	public void setFacultyName(String facultyName) {
		this.facultyName = facultyName;
	}
	public String getSemesterName() {
		return semesterName;
	}
	public void setSemesterName(String semesterName) {
		this.semesterName = semesterName;
	}
	public String getIsEnrolled() {
		return isEnrolled;
	}
	public void setIsEnrolled(String isEnrolled) {
		this.isEnrolled = isEnrolled;
	}
	public String getGrade() {
		return grade;
	}
	public void setGrade(String grade) {
		this.grade = grade;
	}
	
	

}
